package com.specter.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import lombok.extern.slf4j.Slf4j;

/**
 * Note:数据库连接的相关操作，SQL语句的操作，请使用commons-db <br/>
 * <br/>
 * 
 * &lt;dependency&gt;<br/>
 * <br/>
 * &nbsp;&nbsp;&nbsp;&nbsp; &lt;groupId&gt;commons-dbutils&lt;/groupId&gt;<br/>
 * &nbsp;&nbsp;&nbsp;&nbsp;
 * &lt;artifactId&gt;commons-dbutils&lt;/artifactId&gt;<br/>
 * &nbsp;&nbsp;&nbsp;&nbsp; &lt;version&gt;1.6&lt;/version&gt;<br/>
 * &lt;/dependency&gt;<br/>
 * <br/>
 * 
 * <b>{@link org.apache.commons.dbutils.QueryRunner}</b>
 * 
 * @author Liang.Wang
 * @version Dec 27, 2016
 */
@Slf4j
public class JDBCUtils {

	public static String DRIVER_MYSQL = "com.mysql.cj.jdbc.Driver";
	public static String DRIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";
	public static String DRIVER_SQLSERVERL = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

	/**
	 * 按照连接字符串来获取数据库连接，字符串格式为：<br/>
	 * 如：jdbc:mysql://127.0.0.1:3306/test|${username}|${password} <br/>
	 * <br/>
	 * <b style="color:red;">中间用‘|’进行分割，且顺序不能错！</b>
	 * 
	 * @param url_usr_psw 连接字符串 注意说明
	 * @return 数据库连接
	 * @throws Exception 数据库连接错误！
	 */
	public static Connection getConnection(String url_usr_psw) throws Exception {
		String[] s = url_usr_psw.split("\\|");
		String url = s[0], username = s[1], password = s[2];
		log.info("url:{}||username:{}||pwd:{}", url, username, password);

		if (url.contains("mysql")) {
			Class.forName(DRIVER_MYSQL);
		} else if (url.contains("oracle")) {
			Class.forName(DRIVER_ORACLE);
		} else if (url.contains("sqlserver")) {
			Class.forName(DRIVER_SQLSERVERL);
		} else {
			throw new Exception("not support this driver!");
		}

		java.util.Properties props = new java.util.Properties();
		props.setProperty("user", username);
		props.setProperty("password", password);
		props.setProperty("remarks", "true"); // 设置可以获取remarks信息
		props.setProperty("useInformationSchema", "true");// 设置可以获取tables remarks信息
		// 从数据源中获取数据库连接
		return DriverManager.getConnection(url, props);

		// return DriverManager.getConnection(url, username, password);
	}

	/**
	 * 按照 资源文件的配置项获取连接<br/>
	 * ${dsName}.driver.class <br/>
	 * ${dsName}.driver.url <br/>
	 * ${dsName}.driver.username <br/>
	 * ${dsName}.driver.password
	 * 
	 * @param dsName 通用配置前面的名字
	 * @return 数据库连接
	 */
	public static Connection getConnectionByProps(String dsName) {
		Connection connection = null;
		String url = PropsUtils.getValue(dsName + ".driver.url");
		String username = PropsUtils.getValue(dsName + ".driver.username");
		String password = PropsUtils.getValue(dsName + ".driver.password");
		try {
			Class.forName(PropsUtils.getValue(dsName + ".driver.class"));

			java.util.Properties props = new java.util.Properties();
			props.setProperty("user", username);
			props.setProperty("password", password);
			props.setProperty("remarks", "true"); // 设置可以获取remarks信息
			props.setProperty("useInformationSchema", "true");// 设置可以获取tables remarks信息
			// 从数据源中获取数据库连接
			return DriverManager.getConnection(url, props);

			// return DriverManager.getConnection(url, username, password);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static void release(Connection conn) {
		if (conn != null) {
			try {
				conn.close();// 将Connection连接对象还给数据库连接池
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public static void release(Statement st) {
		if (st != null) {
			try {
				st.close();// 关闭负责执行SQL命令的Statement对象
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public static void release(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();// 关闭存储查询结果的ResultSet对象
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 释放Connection,Statement,ResultSet
	 * 
	 * @param conn
	 * @param st
	 * @param rs
	 */
	public static void release(Connection conn, Statement st, ResultSet rs) {
		release(rs);
		release(st);
		release(conn);
	}

}
